﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Mime;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DevExpress.XtraPrinting.Native;
using UdrinkWater_Project.Officer;

namespace UdrinkWater_Project.Admin
{
    public partial class ShopFront : System.Web.UI.Page
    {
        string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        ShopFrontDataContext db = new ShopFrontDataContext();
        public static DataTable dtOrderList = new DataTable();
        public static int dtConut = 0;
        public static DateTime Order_Date_ID;
        public static string OrderID;
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {
                Order_Date_ID = DateTime.Now;
                OrderID = Order_Date_ID.ToString("yyyy-MM-dd HH:mm:ss.fff", new System.Globalization.CultureInfo("en-US"));
                dtOrderList = new DataTable();
                string T1 = Order_Date_ID.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH"));
                string T2 = Order_Date_ID.ToString("HH:mm:ss", new System.Globalization.CultureInfo("en-US"));
                string T3 = Order_Date_ID.ToString("yyyy-MM-dd", new System.Globalization.CultureInfo("en-US"));
                txtOrderDate.Text = T1 + " " + T2;
                DataTable num = GetOrderNum(T3);
                txtListOrder.Text = num.Rows.Count.ToString();
                DataTable Total = GetMoneyTotal(T3);
                float NumIN = 0;
                for (int i = 0; i < Total.Rows.Count; i++)
                {
                    NumIN = NumIN + float.Parse(Total.Rows[i]["Total"].ToString());
                }
                ASPxTextBox1.Text = NumIN.ToString();
            }
            else
            {

            }
        }

        protected void btEmpSert_Click(object sender, EventArgs e)
        {

        }

        protected void btCustomerSert_Click(object sender, EventArgs e)
        {

        }
        private DataTable GetOrderNum(string Today)
        {
            string sqltext = @"SELECT [Order_Date]
                            ,[Customer_ID]
                            ,[Shipped_date]
                            ,[Delivery_Address]
                            ,[Status]
                            ,[Employee_ID]
                            ,[Status_Unpaid]
                            FROM [TB_Order]
                        WHERE Order_Date BETWEEN '"+Today+" 00:00:00.000' AND '"+Today+" 23:59:59.997' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        private DataTable GetMoneyTotal(string Today)
        {
            string sqltext = @"SELECT [Order_Date]
                            ,[Product_ID]
                            ,[Product_Count]
                            ,[Price]
                            ,[Discount]
                            ,((Price * Product_Count) - Discount) AS Total
                            FROM [TB_Orderlist]
                        WHERE Order_Date BETWEEN '" + Today + " 00:00:00.000' AND '" + Today + " 23:59:59.997' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        protected void ASPxGridView2_RowCommand(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewRowCommandEventArgs e)
        {
            DataTable DataEmp = new DataTable();
            if (e.CommandArgs.CommandName == "Cmd_Select")
            {
                string Id = e.CommandArgs.CommandArgument.ToString();
                DataEmp = GetDetialEmp(Id);
                txtEmpID.Text = DataEmp.Rows[0]["ID_Card"].ToString();
                txtEmpName.Text = DataEmp.Rows[0]["Name"].ToString();
            }
        }
        private DataTable GetDetialEmp(string ID_Card)
        {
            string sqltext = @"SELECT ID_Card,( Prefix + '' + Firstname + ' ' + Lastname ) AS Name FROM TB_Employee
                               WHERE [ID_Card] ='" + ID_Card + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }

        protected void ASPxGridView3_RowCommand(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewRowCommandEventArgs e)
        {
            DataTable DataCus = new DataTable();
            if (e.CommandArgs.CommandName == "Cmd_Select")
            {
                string Id = e.CommandArgs.CommandArgument.ToString();
                DataCus = GetDetialCus(Id);
                txtCustomerID.Text = DataCus.Rows[0]["ID_Card"].ToString();
                txtCusName.Text = DataCus.Rows[0]["Name"].ToString();
                txtCusAddress.Text = DataCus.Rows[0]["Adress"].ToString();
                ASPxComboBox1.Value = DataCus.Rows[0]["PROVINCE_ID"];
                ASPxComboBox2.Value = DataCus.Rows[0]["AMPHUR_ID"];
                ASPxComboBox3.Value = DataCus.Rows[0]["DISTRICT_ID"];
                ASPxTextBox2.Text   = DataCus.Rows[0]["Zipcode"].ToString();


            }
        }
        private DataTable GetDetialCus(string ID_Card)
        {
            string sqltext = @"SELECT        ID_Card, Prefix + ' ' + Firstname + ' ' + Lastname AS Name, Adress, Zipcode, DISTRICT_ID, AMPHUR_ID, PROVINCE_ID
FROM                                         TB_Customer
                               WHERE  ID_Card ='" + ID_Card + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }

        protected void ASPxGridView4_RowCommand(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewRowCommandEventArgs e)
        {
            DataTable DataPro = new DataTable();
            DataTable InStock = new DataTable();
            DataTable OutStock = new DataTable();
            if (e.CommandArgs.CommandName == "Cmd_Select")
            {
                string Id = e.CommandArgs.CommandArgument.ToString();
                InStock = GetInstock(Id);
                OutStock = GetOutstock(Id);
                int NumIN = 0;
                int NumOUT = 0;
                for (int i = 0; i < InStock.Rows.Count; i++)
                {

                    NumIN = NumIN + Convert.ToInt32(InStock.Rows[i]["Product_Count"].ToString());
                }
                for (int i = 0; i < OutStock.Rows.Count; i++)
                {
                    NumOUT = NumOUT + Convert.ToInt32(OutStock.Rows[i]["Product_Count"].ToString());
                }
               
                DataPro = GetDetialPro(Id);
                txtProductId.Text = DataPro.Rows[0]["Product_ID"].ToString();
                txtProductName.Text = DataPro.Rows[0]["Product_name"].ToString();
                txtInStock.Text = (NumIN - NumOUT).ToString();
                txtUnit.Text = DataPro.Rows[0]["Unit_Name"].ToString();
                float price = float.Parse(DataPro.Rows[0]["Price"].ToString());
                ASPxImage1.ImageUrl = DataPro.Rows[0]["Image_Path"].ToString();
                txtUnitPrice.Text = price.ToString("0.00");
                txtCount.Focus();
            }
        }
        private DataTable GetOutstock(string Product_ID)
        {
            string sqltext = @"SELECT        Product_ID, Product_Count
                               FROM          TB_StockProductOUT
                               WHERE         Product_ID = '" + Product_ID + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        private DataTable GetInstock(string Product_ID)
        {
            string sqltext = @"SELECT        Product_ID, Product_Count
                               FROM          TB_StockProductIN
                               WHERE         Product_ID = '" + Product_ID + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        private DataTable GetDetialPro(string Product_ID)
        {
            string sqltext = @"SELECT TB_Product.Product_ID, TB_Product.Product_name, TB_Unit_Category.Unit_Name, 
                               TB_Product.Price, TB_Product.In_Stock, TB_Product.Image_Path 
                               FROM TB_Product LEFT JOIN TB_Unit_Category ON TB_Product.Unit = TB_Unit_Category.Unit_ID
                               WHERE TB_Product.Product_ID ='" + Product_ID + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }

        protected void btAddList_Click(object sender, EventArgs e)
        {
            lbWranList.Visible = false;
            if (dtOrderList != null && dtOrderList.Rows.Count == 0)
            {
                DataRow drOrderRow = null;
                if (dtConut == 0)
                {
                    dtOrderList = new DataTable();
                    dtOrderList.Columns.Add(new DataColumn("Product_ID", typeof(string)));
                    dtOrderList.Columns.Add(new DataColumn("Product_Name", typeof(string)));
                    dtOrderList.Columns.Add(new DataColumn("Unit", typeof(string)));
                    dtOrderList.Columns.Add(new DataColumn("UnitPrice", typeof(float)));
                    dtOrderList.Columns.Add(new DataColumn("Count", typeof(int)));
                    dtOrderList.Columns.Add(new DataColumn("Discount", typeof(float)));
                }
                else
                {
                    dtOrderList = new DataTable();
                    dtOrderList.Columns.Add(new DataColumn("Product_ID", typeof(string)));
                    dtOrderList.Columns.Add(new DataColumn("Product_Name", typeof(string)));
                    dtOrderList.Columns.Add(new DataColumn("Unit", typeof(string)));
                    dtOrderList.Columns.Add(new DataColumn("UnitPrice", typeof(float)));
                    dtOrderList.Columns.Add(new DataColumn("Count", typeof(int)));
                    dtOrderList.Columns.Add(new DataColumn("Discount", typeof(float)));
                }

                drOrderRow = dtOrderList.NewRow();
                drOrderRow["Product_ID"] = txtProductId.Text;
                drOrderRow["Product_Name"] = txtProductName.Text;
                drOrderRow["Unit"] = txtUnit.Text;
                drOrderRow["UnitPrice"] = float.Parse(txtUnitPrice.Text);
                drOrderRow["Count"] = Int32.Parse(txtCount.Text);
                drOrderRow["Discount"] = float.Parse(txtDiscount.Text);
                dtOrderList.Rows.Add(drOrderRow);
                GV_OrderList.DataSource = dtOrderList;
                GV_OrderList.DataBind();
                dtConut = 1;
                //Session["TablePrice"] = dtOrderList;
            }
            else if (dtOrderList != null && dtOrderList.Rows.Count > 0)
            {
                dtConut = 0;
                for (int i = 0; i < dtOrderList.Rows.Count; i++)
                {
                    if (dtOrderList.Rows[i]["Product_ID"].ToString() == txtProductId.Text)
                    {
                        lbWranList.Visible = true;
                        dtConut = 1;
                    }
                }
                if (dtConut != 1)
                {
                    DataRow drOrderRow = dtOrderList.NewRow();
                    drOrderRow["Product_ID"] = txtProductId.Text;
                    drOrderRow["Product_Name"] = txtProductName.Text;
                    drOrderRow["Unit"] = txtUnit.Text;
                    drOrderRow["UnitPrice"] = float.Parse(txtUnitPrice.Text);
                    drOrderRow["Count"] = Int32.Parse(txtCount.Text);
                    drOrderRow["Discount"] = float.Parse(txtDiscount.Text);
                    dtOrderList.Rows.Add(drOrderRow);
                    GV_OrderList.DataSource = dtOrderList;
                    GV_OrderList.DataBind();
                    //Session["TablePrice"] = dtOrderList;
                }

            }
            txtProductId.Text = "";
            txtProductName.Text = "";
            txtUnit.Text = "";
            txtUnitPrice.Text = "";
            txtCount.Text = "";
            txtDiscount.Text = "";
            txtInStock.Text = "";
            
        }

        protected void GV_OrderList_CustomUnboundColumnData(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewColumnDataEventArgs e)
        {

            if (e.Column.FieldName == "TotalRow")
            {
                int Count = (int)e.GetListSourceFieldValue("Count");
                float UnitPrice = (float)e.GetListSourceFieldValue("UnitPrice");
                float Discount = (float)e.GetListSourceFieldValue("Discount");
                e.Value = (Count * UnitPrice) - Discount;
            }

        }

        protected void GV_OrderList_RowCommand(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewRowCommandEventArgs e)
        {
            if (e.CommandArgs.CommandName == "Cmd_Delete")
            {
                // Response.Redirect("PromotionLogin.aspx");
                string Index = e.CommandArgs.CommandArgument.ToString();
                DataTable dt = dtOrderList;
                dt.Rows[Convert.ToInt32(Index)].Delete();
                GV_OrderList.DataSource = dt;
                GV_OrderList.DataBind();
            }
        }

        protected void GV_OrderList_FocusedRowChanged(object sender, EventArgs e)
        {

        }

        protected void GV_OrderList_SummaryDisplayText(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewSummaryDisplayTextEventArgs e)
        {
            if (e.Value != null)
            {
                float to = float.Parse(e.Value.ToString());
                txtTotal.Text = to.ToString("0.00");
            }

        }

        protected void btNewOrder_Click(object sender, EventArgs e)
        {
            dtOrderList = new DataTable();
            Response.Redirect("ShopFront.aspx");
        }

        protected void ASPxButton1_Click(object sender, EventArgs e)
        {
            float a = float.Parse(txtTotal.Text);
            float b = float.Parse(txtgetMoney.Text);
            txtCASH.Text = (b - a).ToString("0.00");

        }

        protected void btSaveAndReport_Click(object sender, EventArgs e)
        {

            if (dtOrderList != null && dtOrderList.Rows.Count > 0)
            {
                DataTable dt = new DataTable();
                dt = dtOrderList;


                DataTable FindEmp;
                FindEmp = GetDetialEmp(txtEmpID.Text);
                if (txtEmpID.Text == "" || FindEmp.Rows.Count == 0)
                {
                    // lbWarnEmp.Visible = true;
                }
                else
                {

                    string T1 = Order_Date_ID.ToString("yyyy-MM-dd", new System.Globalization.CultureInfo("en-US"));
                    string T2 = Order_Date_ID.ToString("HH:mm:ss.fff", new System.Globalization.CultureInfo("en-US"));
                    string sD = DateTime.Now.AddDays(3).ToString("yyyy-MM-dd HH:mm:ss", new System.Globalization.CultureInfo("en-US"));
                    TB_Order od = new TB_Order();
                    od.Employee_ID = txtEmpID.Text;
                    if (txtCustomerID.Text == "")
                    {
                        od.Customer_ID = txtCusName.Text;
                        Session["GuestEmp"] = "true";
                    }
                    else
                    {
                        od.Customer_ID = txtCustomerID.Text;   //Session["User"].ToString();
                        Session["GuestEmp"] = "false";
                    }
                    od.Order_Date = Order_Date_ID;
                    od.Shipped_date = DateTime.Now.AddDays(3);
                    od.Delivery_Address = txtCusAddress.Text;
                    od.Status_Buy = 2;//หน้าร้าน
                    od.Status = 1;//ยังไม่ส่ง
                    od.Province_ID = Int32.Parse(ASPxComboBox1.Value.ToString());
                    od.Aumphur_ID = Int32.Parse(ASPxComboBox2.Value.ToString());
                    od.District_ID = int.Parse(ASPxComboBox3.Value.ToString());
                    od.Zipcode = Int32.Parse(ASPxTextBox2.Text);
                    if (rbtStsatusUnpaid.Checked)
                    {
                        od.Status_Unpaid = 1;//ชำระ
                    }
                    else
                    {
                        od.Status_Unpaid = 2;//ยังไม่ชำระ
                        InsertsUnpaid(OrderID);

                    }
                    db.TB_Orders.InsertOnSubmit(od);
                    db.SubmitChanges();              
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string sqltext1 = @"INSERT INTO [TB_Orderlist]
                                            ([Order_Date]
                                            ,[Product_ID]
                                            ,[Product_Count]
                                            ,[Price]
                                            ,[Discount])                                             
                                            values
                                    (@Order_Date,@Product_ID,@Product_Count,@Price,@Discount)";
                        SqlCommand sqlcomm1 = new SqlCommand();
                        SqlConnection sqlconn1 = new SqlConnection(Connection);
                        sqlcomm1.Connection = sqlconn1;
                        sqlcomm1.CommandType = System.Data.CommandType.Text;
                        sqlcomm1.CommandText = sqltext1;
                        sqlcomm1.Parameters.AddWithValue("@Order_Date", OrderID);
                        sqlcomm1.Parameters.AddWithValue("@Product_ID", dt.Rows[i]["Product_ID"].ToString());
                        sqlcomm1.Parameters.AddWithValue("@Product_Count", Convert.ToInt32(dt.Rows[i]["Count"].ToString()));
                        sqlcomm1.Parameters.AddWithValue("@Price", float.Parse(dt.Rows[i]["UnitPrice"].ToString()));
                        sqlcomm1.Parameters.AddWithValue("@Discount", float.Parse(dt.Rows[i]["Discount"].ToString()));
                        try
                        {
                            sqlcomm1.Connection.Open();
                            sqlcomm1.ExecuteNonQuery();
                        }
                        catch (Exception)
                        {
                            sqlcomm1.Connection.Close();
                            sqlcomm1.Connection.Dispose();
                        }
                        sqlcomm1.Connection.Close();
                        sqlcomm1.Connection.Dispose();
                       
                    }
                    string sqltext = @"INSERT INTO [TB_Revenue]
                                    ([Revenue_Date]
                                    ,[Revenue_List]
                                    ,[Revenue_Price])
                                    VALUES
                                    (@Revenue_Date,@Revenue_List,@Revenue_Price)";
                    SqlCommand sqlcomm = new SqlCommand();
                    SqlConnection sqlconn = new SqlConnection(Connection);
                    sqlcomm.Connection = sqlconn;
                    sqlcomm.CommandType = System.Data.CommandType.Text;
                    sqlcomm.CommandText = sqltext;
                    sqlcomm.Parameters.AddWithValue("@Revenue_Date", OrderID);
                    sqlcomm.Parameters.AddWithValue("@Revenue_List", "ขายสินค้า");
                    sqlcomm.Parameters.AddWithValue("@Revenue_Price", float.Parse(txtTotal.Text));
                    try
                    {
                        sqlcomm.Connection.Open();
                        sqlcomm.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        sqlcomm.Connection.Close();
                        sqlcomm.Connection.Dispose();
                    }
                    sqlcomm.Connection.Close();
                    sqlcomm.Connection.Dispose();
                    Session["Date"] = T1.ToString();
                    Session["Time"] = T2.ToString();
                    Response.Write("<script>alert('บันทึกรายการเรียบร้อยแล้ว!');window.location='BillOnline.aspx';</script>");
                   
                   
                }

            }
        }
        public static object GetDataValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            return value;
        }
        public void InsertsUnpaid(string Order_ID)
        {
            string sqltext = @"insert into [TB_unpaid] 
                                   ( [Unpaid_Date]
                                    ,[Name]
                                    ,[Address]
                                    ,[District_ID]
                                    ,[Aumphur_ID]
                                    ,[Province_ID]
                                    ,[Count]
                                    ,[UnpaidDeadline_Date]
                                    ,[Act_Officer]
                                    ,[Status_Unpaid]
                                    ,[Referent_Unpaid])
                                   values (@Date,@Name,@Address,@District,@Aumphur,@Province,@Count,@Deadline,@Act_Off,2,@Ref)";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection(Connection);
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            sqlcomm.Parameters.AddWithValue("@Date", GetDataValue(DateTime.Now));
            sqlcomm.Parameters.AddWithValue("@Name", GetDataValue(txtCusName.Text));
            sqlcomm.Parameters.AddWithValue("@Address", GetDataValue(txtCusAddress.Text));
            sqlcomm.Parameters.AddWithValue("@District", GetDataValue(ASPxComboBox3.Value));
            sqlcomm.Parameters.AddWithValue("@Aumphur", GetDataValue(ASPxComboBox2.Value));
            sqlcomm.Parameters.AddWithValue("@Province", GetDataValue(ASPxComboBox1.Value));
            sqlcomm.Parameters.AddWithValue("@Count", GetDataValue(float.Parse(txtTotal.Text)));
            sqlcomm.Parameters.AddWithValue("@Deadline", GetDataValue(DateTime.Now.AddDays(3)));
            sqlcomm.Parameters.AddWithValue("@Act_Off", GetDataValue("1410200089697"));
            sqlcomm.Parameters.AddWithValue("@Ref", GetDataValue(Order_ID));

            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();

        }
        public DataTable GetData_OrderIDlist(string T1, string T2)
        {//" + Session["User"].ToString() + "
            DataTable dt = new DataTable();
            string sqltext = @"SELECT [Order_Date]
                            ,[Customer_ID]
                            ,[Shipped_date]
                            ,[Delivery_Address]
                            ,[Status]
                            ,[Employee_ID]
                             FROM [DB_Udrink].[dbo].[TB_Order]
                             WHERE [Order_Date] ='" + T1 + " " + T2 + "'";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }

        protected void ASPxComboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            ASPxComboBox2.Value = "";
            ASPxComboBox3.Value = "";
        }

        protected void ASPxComboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            ASPxComboBox3.Value = "";
        }



    }
}